Custom Columns allows users to create and share new Hierarchies and Measures against Pyramid Models at run time. This permits the dynamic extension of the Model by users to cater for unforeseen Modeling requirements in terms of model structure, measures and row level calculations.
Custom Columns, as the name implies, create new virtual columns in the underlying Model table definitions which can be used to define new Hierarchies and Measures. The values within the virtual column are calculated at the row level for each row, the calculations defined passed to the underlying database for execution as part of the query by the Pyramid Pyrana Query Engine at run time.
Why Custom Columns are Needed
Pyramid Models define how numerical values are aggregated and viewed by business dimensions. Taking the sales of a commercial company, each transaction will generate a sales value. that sales value will be associated with what product was sold, the date it was sold, to which customer, which salesperson sold it and perhaps many ot business attributes.
Typically when understanding the company performance, the individual sales transactions will be aggregated by these business dimensions. Pyramid Models define how these aggregations are performed to create Measures.
Thus, viewing my Sales by Month and Product, the Pyramid query will aggregate the Sales to show the total Sales for each Product in each Month. In turn, performing calculations on these Measures, the calculations operate on the aggregate values.
For example, to calculate the average sales transaction value I could define the calculation as Sales / Quantity. Viewing this by Month and Country, the calculations will aggregate the Sales and the Quantity for each product within each month, then divide one by the other. The aggregation will take place first, then the division. Because the values inserted into the calculation are defined by the Pyramid Semantic Model in terms of Hierarchies and Measures, this type of operation is known as a Semantic Calculation.
- Click here to learn more about Semantic Models
- Click here to learn more about Semantic Calculation formulas.
However, for certain calculations, where the order of calculation and aggregation is significant, this can lead to erroneous results.
Custom Columns vs Virtual Calculated Columns
Custom Columns and Virtual Calculated Columns are almost identical. The difference lies in where they are created and who can create them.
- Virtual Calculated Columns are built into a semantic model, and are designed by model creators to be accessible to users who can see the model. While access to the virtual columns can be managed via model security (including member level security), the concept is centered around a 'global' calculation that users access in the model itself.
- Custom Columns are built as custom objects and are additive to an existing semantic model. They are designed by end users (more on a case by case basis) and accessible to those users with access to the calculation logic items (and folders) only. Column access can be managed using the content manager and roles, but there is no member level security option.
There is no difference in performance, analytic or mathematical function between the 2 approaches.
The Supermarket Checkout Example
Most shops, and especially supermarkets, have electronic point of sales systems, or EPOS. These terminals calculate the individual shopper's bill and also record into a database details of the bill in terms of the products sold, the quantity and unit price.
For each line of the bill the Sales Line Total is calculated as Quantity * Unit Price. The record is then stored and when store performance is calculated, the Sales Line Total is aggregated to shoe totals by Product by Time and perhaps by Till Operator.
Bur what if the calculation was performed after the aggregation of the values? Total Quantity would be perhaps Average Price * Total Quantity. This would obviously be incorrect.
In order for the calculation to be accurate, Quantity * Price needs to be calculated at the row level of the table. Custom Columns allows users to create and share these row level calculations dynamically at run time.
- Click here to walk through examples of Custom Column calculations